from settings import data1_connection
import datetime
from util import get_stock, get_strategy_position, time_stamp, \
    select_online_bench, time_to_str, float_to_percentage, get_trade_number, str_time_to_time, \
    percentage_to_float, get_real_capital
import time
from pymysql.err import MySQLError


class UserReal:

    def select_user(self, phone):
        """
       用phone找到对应的用户的Id
       :param phone:
       :return: user_id
        """
        cursor, conn = data1_connection()
        sql = "select userId from aq_customer where customer_phone='%s'" % phone
        try:
            cursor.execute(sql)
            conn.commit()
            user = cursor.fetchall()
            user_id = user[0]["userId"]
            if user_id:
                return user_id
        except MySQLError as e:
            print(e)

    def select_running_user(self, phone):
        """
        筛选用户的实盘开启的用户
        :param phone:
        :return:
        """
        cursor, conn = data1_connection()
        sql = "select id from aq_customer where customer_phone='%s'" % phone
        try:
            cursor.execute(sql)
            conn.commit()
            strategy_real = cursor.fetchall()
            if strategy_real[0]["id"]:
                id = strategy_real[0]["id"]
                return id
            else:
                return None
        except MySQLError as e:
            print(e)
            conn.rollback()

    def update_strategy_real(self, data):
        """
        向用户实盘中出入数据
        :param data:
        :return:
        """
        now = datetime.datetime.now().replace(microsecond=0)
        cursor, conn = data1_connection()
        sql = "update strategy_real set accumuReturn='%s', annualReturn='%s', capital=%s, totalPosition='%s', " \
              "maxDrawdown='%s', updatetime='%s', equity='%s', cash='%s' , initialMoney='%s' where id='%s'"
        end_sql = sql % (percentage_to_float(data["accumureturn"]), data["annual_return"],
                         get_real_capital(data["capital"]),
                         percentage_to_float(data["totalPosition"]), percentage_to_float(data["max_down"]),
                         now, data["equity"], data["cash"], data["init_money"], data["real_id"])
        try:
            cursor.execute(end_sql)
            conn.commit()
            print("实盘数据写入成功！%s" % data["real_id"])
        except MySQLError as e:
            conn.rollback()
            print("插入数据失败:%s" % e)

    def delete_history_position_data(self, strategy_id):
        """
        删除历史的持仓数据
        :param strategy_id:
        :return:
        """
        cursor, conn = data1_connection()
        sql = "delete from strategy_real_position where strategyId='%s'" % strategy_id
        try:
            cursor.execute(sql)
            conn.commit()
            print("删除历史持仓数据失败成功：%s" % strategy_id)
            return True
        except MySQLError as e:
            conn.rollback()
            print("删除历史持仓数据失败:%s" % e)
            return False

    def insert_strategy_position(self, position_data):
        """
        向用户持仓中插入数据, 持仓值插入当天的数据, 每天插入数据之前， 要删除历史的持仓数据
        :param data:
        :return:
        """
        flag = self.delete_history_position_data(position_data["real_id"])
        if not flag:
            return
        cursor, conn = data1_connection()
        current = datetime.datetime.now().replace(microsecond=0)
        if not position_data["position"]:
            print("用户当前没有持仓！")
            return
        for data in position_data["position"]:
            name, code = get_stock(data["stock"])
            try:
                sql = "insert into strategy_real_position (strategyId, sharesname, sharescode, strategyPosition, cost, " \
                      "price, sharesReturn, strategyEarnings, createtime) values('%s', '%s', '%s'," \
                      "'%s', '%s', '%s', '%s', '%s', '%s')"

                end_sql = sql % (position_data["real_id"], name, code, get_strategy_position(data["amount"]),
                             data["avgCost"], data["price"], data["gain"],
                             float_to_percentage(data["trueGainPercent"]), current)
            except Exception as e:
                sql = "insert into strategy_real_position (strategyId, sharesname, sharescode, strategyPosition, cost, " \
                      "price, sharesReturn, strategyEarnings, createtime) values('%s', '%s', '%s'," \
                      "'%s', '%s', '%s', '%s', '%f', '%s')"

                end_sql = sql % (position_data["real_id"], name, code, get_strategy_position(data["amount"]),
                             data["avgCost"], data["price"], data["gain"],
                             float_to_percentage(data["trueGainPercent"]), current)

            try:
                cursor.execute(end_sql)
                conn.commit()
                print("插入持仓成功！%s" % position_data["real_id"])
            except MySQLError as e:
                print(e)
                conn.rollback()
                print("插入用户实盘持仓失败 %s" % position_data["real_id"])

    def insert_strategy_real_netvalue(self, yield_curve_data):
        """
        向用户的收益曲线中插入数据
        :param yield_curve_data:
        :return:
        """
        today = time_to_str()
        bench = select_online_bench(today)
        if not bench:
            print("获取当前的hs300的收盘价失败!")
            bench = 0
        cursor, conn = data1_connection()
        current = datetime.datetime.now().replace(microsecond=0)
        bench_mark = yield_curve_data["result"]["benchmark"]
        over_all_return = yield_curve_data["result"]["overallReturn"]
        today_str = today
        for index in range(len(bench_mark["value"])):
            time.sleep(1)
            value = bench_mark["value"][index]
            times = bench_mark["time"][index]
            date = time_stamp(times)
            bench_return = float_to_percentage(value)
            over = over_all_return["value"][index]
            over_return = float_to_percentage(over)
            date_str = datetime.datetime.strftime(date.date(), "%Y-%m-%d")
            if today_str == date_str:
                sql = "insert into strategy_real_netvalue (strategyId, capital, equity, cash, bench, " \
                      "accumuReturn, indexReturn, createtime, dateTime) " \
                      "values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')"
                try:
                    end_sql = sql % (yield_curve_data["real_id"], get_real_capital(yield_curve_data["capital"]),
                                     yield_curve_data["equity"], yield_curve_data["cash"], bench,
                                     over_return, bench_return,
                                     current, date.date())
                except Exception as e:
                    end_sql = sql % (yield_curve_data["real_id"], get_real_capital(yield_curve_data["capital"]),
                                     yield_curve_data["equity"], yield_curve_data["cash"], bench,
                                     float_to_percentage(over_return), float_to_percentage(bench_return),
                                     current, date.date())
                try:
                    cursor.execute(end_sql)
                    conn.commit()
                    print("%s的收益曲线写入成功" % date)
                except Exception as e:
                    print("%s的收益曲线写入失败:" % e)
                    conn.rollback()

    def update_stratey_real_netvalue(self, yield_curve_data):
        """
        每天更新实盘的收益曲线
        :param yield_curve_data:
        :return:
        """
        today = time_to_str()
        bench = select_online_bench(today)
        if not bench:
            print("获取当前的hs300的收盘价失败!")
            bench = 0
        cursor, conn = data1_connection()
        current = datetime.datetime.now().replace(microsecond=0)
        bench_mark = yield_curve_data["result"]["benchmark"]
        over_all_return = yield_curve_data["result"]["overallReturn"]
        # 当前的时间
        today = datetime.datetime.now().date()
        today_str = datetime.datetime.strftime(today, "%Y-%m-%d")
        for index in range(len(bench_mark["value"])):
            time.sleep(1)
            value = bench_mark["value"][index]
            times = bench_mark["time"][index]
            date = time_stamp(times)
            date_str = datetime.datetime.strftime(date.date(), "%Y-%m-%d")
            if today_str == date_str:
                bench_return = float_to_percentage(value)
                over = over_all_return["value"][index]
                over_return = float_to_percentage(over)
                sql = "insert into strategy_real_netvalue (strategyId, capital, equity, cash, bench, " \
                      "accumuReturn, indexReturn, createtime, dateTime) " \
                      "values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')"
                try:
                    end_sql = sql % (yield_curve_data["real_id"], get_real_capital(yield_curve_data["capital"]),
                                     yield_curve_data["equity"], yield_curve_data["cash"], bench,
                                     float_to_percentage(over_return), float_to_percentage(bench_return),
                                     current, date.date())
                except Exception as e:
                    end_sql = sql % (yield_curve_data["real_id"], get_real_capital(yield_curve_data["capital"]),
                                     yield_curve_data["equity"], yield_curve_data["cash"], bench,
                                     float_to_percentage(over_return), float_to_percentage(bench_return),
                                     current, date.date())
                try:
                    cursor.execute(end_sql)
                    conn.commit()
                    print("%s的收益曲线写入成功" % date)
                except Exception as e:
                    print("%s的收益曲线写入成功:%s" % e)
                    conn.rollback()

    def insert_strategy_real_traction(self, transaction_data):
        """
        向用户的交易记录中插入数据
        :param transaction_data:
        :return:
        """
        current = datetime.datetime.now().replace(microsecond=0)
        cursor, conn = data1_connection()
        sql = "insert into strategy_real_push (strategyId, sharesCode, sharesName, tradeTime, direction, " \
              "tradeNumber, tradePrice, tradeTotalPrice, weight, createtime) values('%s', '%s', '%s', '%s', " \
              "'%s', '%s', '%s', '%s', '%s', '%s')"
        data_list = transaction_data["transaction"]
        for data in data_list:
            time.sleep(1)
            if data["status"] == "废单":
                continue
            capital = self.get_netvalue_capital(transaction_data["real_id"], data["date"])
            if not capital:
                print("获取用户实盘的总资产失败！")
                return
            tradeTotalPrice = data["total"]
            if isinstance(tradeTotalPrice,str):
                tradeTotalPrice = tradeTotalPrice.strip("()")
            weight = capital / float(tradeTotalPrice) * 0.997
            name, code = get_stock(data["stock"])
            if data["transaction"] == "买":
                data["transaction"] = 1
            else:
                data["transaction"] = -1
            end_sql = sql % (transaction_data["real_id"], code, name, data["date"], data["transaction"],
                             get_trade_number(data["amount"]), data["price"], float(tradeTotalPrice),
                             weight, current)
            try:
                cursor.execute(end_sql)
                conn.commit()
                print("用户实盘交易数据写入完成！%s" % transaction_data["real_id"])
            except MySQLError as e:
                conn.rollback()
                print("用户的实盘的交易数据写入失败！%s" % transaction_data["real_id"])

    def select_running_real(self, user_id):
        """
        获取用户开启实盘的id
        :param user_id:
        :return:
        """
        cursor, conn = data1_connection()
        sql = "select id from strategy_real where userId='%s' and status=1" % user_id
        try:
            cursor.execute(sql)
            conn.commit()
            real = cursor.fetchall()
            if real:
                real_id = real[0]["id"]
                return real_id
            else:
                return None
        except MySQLError as e:
            print("引发错误：%s" % e)
            return None

    def get_netvalue_capital(self, strategy_id, date):
        """
        按照时间， strategy_id来查找capital
        :param strategy_id:
        :param date:
        :return:
        """
        cursor, conn = data1_connection()
        sql = "select capital from strategy_real_netvalue where " \
              "strategyId='%s' and dateTime='%s'" % (strategy_id, date)
        try:
            cursor.execute(sql)
            conn.commit()
            data = cursor.fetchall()
            if data:
                capital = data[0]["capital"]
                return capital
            else:
                return None
        except MySQLError as e:
            print("查询strategy_real_netvalue的capital失败:%s" % e)
            conn.rollback()
            return None

    def select_old_real(self, user_name):
        """
        数据库查询老用户的user_id
        :param user_name:
        :return:
        """
        cursor, conn = data1_connection()
        sql = "select id from customer where telphone='%s'" % user_name
        try:
            cursor.execute(sql)
            conn.commit()
            data = cursor.fetchall()
            if data:
                user_id = data[0]["id"]
                return user_id
            return None
        except MySQLError as e:
            print(e)
            conn.rollback()

    def insert_strategy_real(self, data):
        """
        向用户实盘中出入数据
        :param data:
        :return:
        """
        now = datetime.datetime.now().replace(microsecond=0)
        cursor, conn = data1_connection()
        sql = "insert into strategy_real (userId, strategyId, strategyName, " \
              "accumuReturn, annualReturn, capital, totalPosition, " \
              "maxDrawdown, updatetime, equity, cash) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', " \
              "'%s', '%s', '%s', '%s')"
        end_sql = sql % (0, 0, data["strategy_name"], percentage_to_float(data["accumureturn"]), data["annual_return"],
                         get_real_capital(data["capital"]),
                         percentage_to_float(data["totalPosition"]), percentage_to_float(data["max_down"]),
                         now, data["equity"], data["cash"])
        try:
            cursor.execute(end_sql)
            strategy_real_id = cursor.lastrowid
            conn.commit()
            print("老用户实盘数据写入成功！%s" % data["user_id"])
            return strategy_real_id
        except MySQLError as e:
            conn.rollback()
            print("老用户插入实盘数据失败:%s" % e)

    def select_is_real(self, phone):
        """
        查看用户是否是第一次插入数据
        :param phone:
        :return:
        """
        cursor, conn = data1_connection()
        sql = "select strategy_real_id from old_customer_real where customer_phone='%s'" % phone
        try:
            cursor.execute(sql)
            conn.commit()
            data = cursor.fetchall()
            if data:
                real_id = data[0]["strategy_real_id"]
                return real_id
            else:
                return None
        except MySQLError as e:
            print(e)
            conn.rollback()

    def insert_associated(self, user_phone, real_id):
        """
        管理老用户和实盘的数据
        :param user_id:
        :param real_id:
        :return:
        """
        cursor, conn = data1_connection()
        create_time = datetime.datetime.now().replace(microsecond=0)
        sql = "insert into old_customer_real (customer_phone, strategy_real_id, create_time) " \
              "values ('%s', '%s', '%s')"
        end_sql = sql % (user_phone, real_id, create_time)
        try:
            cursor.execute(end_sql)
            conn.commit()
            print("关联老用户%s和实盘的成功！" % user_phone)
        except MySQLError as e:
            print("关联老用户%s和实盘的失败！%s" % (user_phone, e))
            conn.rollback()


user_real = UserReal()